interface TableColumn {
  name: string;
  type: string;
}

interface Table {
  name: string;
  schema: string;
  columns: TableColumn[];
}

import tables from "./surface/tables";

class SQLite {
  db: any;
  dbName: string;

  constructor(dbName: string) {
    this.dbName = dbName; // 数据库名称
  }

  // 初始化数据库
  async init(): Promise<void> {
    return new Promise((resolve, reject) => {
      console.log(`初始化数据库: ${this.dbName}`);

      // 检查数据库是否已经打开
      if (this.isOpenDB(this.dbName)) {
        console.warn(`数据库已经打开: ${this.dbName}`);
        resolve(); // 已经打开，直接返回
        return;
      }

      // 打开新的数据库连接
      this.db = plus.sqlite.openDatabase({
        name: this.dbName,
        path: `_doc/${this.dbName}.db`,
        success: (e: any) => {
          console.warn("数据库打开成功 success!");
          resolve(); // 打开成功，返回
        },
        fail: (e: any) => {
          console.warn("openDatabase failed: " + JSON.stringify(e));
          reject("openDatabase failed: " + JSON.stringify(e));
        },
      });
    });
  }

  // 判断数据库是否已经打开
  private isOpenDB(name: string): boolean {
    return plus.sqlite.isOpenDatabase({
      name: name,
      path: `_doc/${name}.db`, // 确保路径一致
    });
  }

  // 创建所有表
  async createTables(): Promise<void> {
    for (const table of tables) {
      await this.createTableIfNotExists(table.name, table.schema);
      for (const column of table.columns) {
        await this.addColumnIfNotExists(table.name, column.name, column.type);
      }
    }
  }

  // 添加列前检查列是否存在
  private async addColumnIfNotExists(
    tableName: string,
    columnName: string,
    columnType: string
  ): Promise<void> {
    console.log(tableName, columnName, columnType);
    try {
      // 检查列是否存在
      const exists = await this.checkColumn(tableName, columnName);
      // 如果列不存在，添加新列
      if (!exists) {
        const alterSql = `ALTER TABLE ${tableName} ADD COLUMN ${columnName} ${columnType}`;
        await this.executeSql(alterSql);
        console.log(`列 ${columnName} 已成功添加到表 ${tableName}`);
      } else {
        console.log(`列 ${columnName} 已存在于表 ${tableName}`);
      }
    } catch (error) {
      console.error(`添加列失败: ${(error as Error).message || error}`);
    }
  }

  // 检查列是否存在的函数
  private async checkColumn(
    tableName: string,
    columnName: string
  ): Promise<boolean> {
    const sql = `PRAGMA table_info(${tableName})`;

    try {
      const results = await new Promise<any[]>((resolve, reject) => {
        plus.sqlite.selectSql({
          name: this.dbName,
          sql,
          success: (results: any[]) => resolve(results),
          fail: (error: any) => {
            console.error("查询失败:", error);
            reject(error);
          },
        });
      });

      // 查询成功，判断列是否存在
      return results.some((row) => row.name === columnName);
    } catch (error) {
      console.error("检查列失败:", error);
      return false;
    }
  }

  // 创建表格，如果表格不存在则创建
  private async createTableIfNotExists(
    tableName: string,
    tableStructure: string
  ): Promise<void> {
    const exists = await this.checkTableExists(tableName);
    if (!exists) {
      const createSql = `CREATE TABLE ${tableName} (${tableStructure})`;
      await this.executeSql(createSql);
      console.warn(`表 ${tableName} 创建成功`);
    } else {
      console.warn(`表 ${tableName} 已经存在`);
    }
  }

  // 检查表是否存在
  private async checkTableExists(tableName: string): Promise<boolean> {
    const sql = `SELECT name FROM sqlite_master WHERE type='table' AND name='${tableName}'`;

    try {
      const results = await new Promise<any[]>((resolve, reject) => {
        plus.sqlite.selectSql({
          name: this.dbName,
          sql,
          success: (results: any[]) => resolve(results),
          fail: (error: any) => {
            console.error("查询失败:", error);
            reject(error);
          },
        });
      });

      // 如果表存在，则返回 true
      return results.length > 0;
    } catch (error) {
      console.error("检查表失败:", error);
      return false;
    }
  }

  // 获取表字段名
  private async getTableColumns(tableName: string): Promise<string[]> {
    const sql = `PRAGMA table_info(${tableName})`; // 使用 PRAGMA 获取表结构信息

    try {
      const results = await new Promise<any[]>((resolve, reject) => {
        plus.sqlite.selectSql({
          name: this.dbName,
          sql,
          success: (results: any[]) => resolve(results),
          fail: (error: any) => {
            console.error("查询表字段失败:", error);
            reject(error);
          },
        });
      });

      // 从查询结果中提取列名
      return results.map((row) => row.name);
    } catch (error) {
      console.error("获取表字段失败:", error);
      return [];
    }
  }

  async saveData(
    storeName: string,
    data: Record<string, any>[]
  ): Promise<string> {
    let myUserInfo = uni.getStorageSync("userInfo");
    let myUserId = myUserInfo ? myUserInfo.user_id : null;

    if (!myUserId) {
      throw new Error("未找到用户信息，无法保存数据");
    }

    // 获取表的所有字段名
    const columns = await this.getTableColumns(storeName);

    const insertPromises = data.map((item) => {
      item.myUserId = myUserId;

      // 筛选出存在于表中的字段
      const filteredItem = Object.keys(item).reduce((acc, key) => {
        if (columns.includes(key)) {
          acc[key] = item[key];
        }
        return acc;
      }, {} as Record<string, any>);

      const keys = Object.keys(filteredItem);
      const values = keys.map((key) => {
        let value = filteredItem[key];

        // 如果值为 null 或 undefined，转为 SQL 中的 NULL
        if (value === null || value === undefined) {
          return "NULL"; // 返回 SQL 中的 NULL
        }

        // 如果值是对象类型，则转为 JSON 字符串
        if ((value && typeof value === "object") || Array.isArray(value)) {
          value = JSON.stringify(value);
        }

        // 对于字符串类型字段，如果是空字符串并且允许 NULL，插入 NULL
        if (value === "") {
          return "NULL"; // 将空字符串转为 NULL
        }

        // 对于其他情况，正常处理
        return `'${value}'`;
      });

      // 生成插入 SQL 语句
      const sql = `INSERT OR REPLACE INTO ${storeName} (${keys.join(
        ", "
      )}) VALUES (${values.join(", ")})`;

      console.warn("数据保存准备:" + storeName, item);
      return new Promise<void>((resolve, reject) => {
        const sqlArray = Array.isArray(sql) ? sql : [sql];
        plus.sqlite.executeSql({
          name: this.dbName,
          sql: sqlArray,
          success: () => {
            console.warn("数据保存成功:" + storeName, item);
            resolve();
          },
          fail: (error: any) => {
            console.warn("数据保存失败:", error);
            reject(`保存数据到 ${storeName} 失败: ${error.message}`);
          },
        });
      });
    });

    await Promise.all(insertPromises);
    return `数据保存成功到 ${storeName}`;
  }

  // 更新数据
  async updateData(
    storeName: string,
    filters: Record<string, any>, // 需要更新的数据的条件
    updates: Record<string, any> // 需要更新的数据
  ): Promise<string> {
    // 获取用户信息
    let myUserInfo = uni.getStorageSync("userInfo");
    let myUserId = myUserInfo ? myUserInfo.user_id : null;

    if (!myUserId) {
      throw new Error("未找到用户信息，无法更新数据");
    }

    // 获取表的所有字段名
    const columns = await this.getTableColumns(storeName);

    let setStr = "";
    let whereStr = "";

    // 生成 SET 子句（更新的字段和值），只保留表中已有的字段
    let updateKeys = Object.keys(updates);
    updateKeys.forEach((key, index) => {
      // 确保字段值是对象或数组时转换为 JSON 字符串
      if (columns.includes(key)) {  // 只处理表中存在的字段
        const value = updates[key];
        const valueStr =
          typeof value === "object" || Array.isArray(value)
            ? JSON.stringify(value)
            : value;

        setStr += `${key} = ${JSON.stringify(valueStr)}${index < updateKeys.length - 1 ? ", " : ""}`;
      }
    });

    // 生成 WHERE 子句（匹配的条件）
    let filterKeys = Object.keys(filters);
    filterKeys.forEach((key, index) => {
      // 确保条件中的字段值是对象或数组时转换为 JSON 字符串
      const value = filters[key];
      const valueStr =
        typeof value === "object" || Array.isArray(value)
          ? JSON.stringify(value)
          : value;

      whereStr += `${key} = ${JSON.stringify(valueStr)}${index < filterKeys.length - 1 ? " AND " : ""}`;
    });

    // 确保只有当符合条件且属于当前用户的数据被更新
    whereStr += ` AND myUserId = ${myUserId}`;

    // 构建更新 SQL 语句
    const updateSql = `UPDATE ${storeName} SET ${setStr} WHERE ${whereStr}`;
    console.warn("数据更新:" + updateSql);

    return new Promise((resolve, reject) => {
      // #ifdef APP-PLUS
      const sqlArray = Array.isArray(updateSql) ? updateSql : [updateSql];
      plus.sqlite.executeSql({
        name: this.dbName,
        sql: sqlArray,
        success: (e: any) => {
          console.warn("数据更新成功:", updateSql);
          resolve(e);
        },
        fail: (e: any) => {
          console.error("数据更新失败:", e);
          reject(e);
        },
      });
      // #endif
    });
  }


  // 删除数据
  async deleteData(
    tableName: string,
    delData: Record<string, any> | string = {}
  ): Promise<any> {
    let myUserInfo = uni.getStorageSync("userInfo");
    let myUserId = myUserInfo ? myUserInfo.user_id : null;

    let setStr = "";

    // 如果传入的是字符串，直接使用
    if (typeof delData === "string") {
      setStr = delData;
    } else if (Object.keys(delData).length > 0) {
      // 如果传入的是对象，构建 SQL 条件
      setStr = Object.keys(delData)
        .map((key) => `${key} = ${JSON.stringify(delData[key])}`)
        .join(" AND ");
    }

    // 添加 myUserId 条件
    setStr += setStr ? ` AND myUserId = ${myUserId}` : `myUserId = ${myUserId}`;

    const deleteSql = `DELETE FROM ${tableName} WHERE ${setStr}`;

    return new Promise((resolve, reject) => {
      // #ifdef APP-PLUS
      const sqlArray = Array.isArray(deleteSql) ? deleteSql : [deleteSql];

      plus.sqlite.executeSql({
        name: this.dbName,
        sql: sqlArray,
        success: (e: any) => {
          console.warn("数据删除成功:", deleteSql);
          resolve(e);
        },
        fail: (e: any) => {
          console.log(e);
          reject(e);
        },
      });
      // #endif
    });
  }

  // 批量删除数据
  async deleteMultiple(storeName: string, ids: string[]): Promise<string> {
    const deletePromises = ids.map((id) => this.deleteData(storeName, id));
    await Promise.all(deletePromises);
    return "批量删除成功";
  }

  // 查询数据详情
  async getDataDetails(
    tableName: string,
    queryData: Record<string, any> = {}
  ): Promise<any | null> {
    let whereStr = "";
    if (typeof queryData === "string") {
      whereStr = queryData;
    } else if (Object.keys(queryData).length) {
      const dataKeys = Object.keys(queryData);
      dataKeys.forEach((item, index) => {
        whereStr += `${item} = ${JSON.stringify(queryData[item])}${dataKeys.length - 1 !== index ? " and " : ""
          }`;
      });
    }
    const selectSql = `SELECT * FROM ${tableName} WHERE ${whereStr}`;

    return new Promise((resolve, reject) => {
      // #ifdef APP-PLUS
      plus.sqlite.selectSql({
        name: this.dbName,
        sql: selectSql,
        success: (data: any) => {
          console.warn("数据库查询数据详情-------", selectSql);
          resolve(data.length ? data[0] : null);
        },
        fail: (e: any) => {
          console.log("查询失败：", e);
          reject(e);
        },
      });
      // #endif
    });
  }

  // 查询数据
  async loadData(
    storeName: string,
    setDatasql: {
      page?: number;
      limit?: number;
      sortField?: string;
      sortOrder?: string;
      [key: string]: any;
    }
  ): Promise<any[]> {
    if (!this.isOpenDB(this.dbName)) {
      throw new Error("数据库未初始化或未打开");
    }

    return new Promise((resolve, reject) => {
      const page = setDatasql.page || 1; // 默认页码为 1
      const limit =
        setDatasql.limit !== undefined ? setDatasql.limit : Infinity; // 默认查询所有数据
      const offset = (page - 1) * limit; // 计算偏移量
      const sortField = setDatasql.sortField || "Time"; // 默认排序字段为 'Time'
      const sortOrder = setDatasql.sortOrder || "desc"; // 默认排序方式为升序

      // 获取 myUserId
      let myUserInfo = uni.getStorageSync("userInfo");
      let myUserId = myUserInfo ? myUserInfo.user_id : null;

      let sql = `SELECT * FROM ${storeName}`;
      console.log('sql-------' + sql + myUserId);
      let conditions: string[] = [];

      // 处理过滤条件，包括 $ne
      Object.entries(setDatasql).forEach(([key, value]) => {
        if (
          key !== "page" &&
          key !== "limit" &&
          key !== "sortField" &&
          key !== "sortOrder"
        ) {
          // 处理 $ne (不等于)
          if (value && value.$ne !== undefined) {
            conditions.push(`${key} != ${JSON.stringify(value.$ne)}`);
          } else {
            conditions.push(`${key} = ${JSON.stringify(value)}`);
          }
        }
      });

      // 添加 myUserId 作为过滤条件
      if (myUserId) {
        conditions.push(`myUserId = ${JSON.stringify(myUserId)}`);
      }

      // 如果有条件，拼接 WHERE
      if (conditions.length) {
        sql += " WHERE " + conditions.join(" AND ");
      }

      // 判断如果 storeName 是 "friendList"，就不使用 Time 排序
      if (storeName === "friendList") {
        sql += ` ORDER BY id ${sortOrder.toUpperCase()}`; // 使用 id 作为默认排序字段
      } else {
        // 如果不是 friendList，按 Time 排序
        sql += ` ORDER BY ${sortField} ${sortOrder.toUpperCase()}`;
      }

      // 如果有分页限制，则加上 LIMIT 和 OFFSET
      if (limit !== Infinity) {
        sql += ` LIMIT ${limit} OFFSET ${offset}`;
      }
      console.log('sql-------33' + sql);

      // 执行 SQL 查询
      plus.sqlite.selectSql({
        name: this.dbName,
        sql,
        success: (result: any) => {
          console.log('9999');
          // 解析查询结果中的 JSON 字符串字段
          const parsedResult = result.map((item: Record<string, any>) => {
            const parsedItem: Record<string, any> = {};
            Object.keys(item).forEach((key) => {
              const value = item[key];

              // 如果字段值是 JSON 字符串，尝试解析它
              try {
                if (
                  value &&
                  typeof value === "string" &&
                  ((value.startsWith("{") && value.endsWith("}")) ||
                    (value.startsWith("[") && value.endsWith("]")))
                ) {
                  parsedItem[key] = JSON.parse(value); // 解析为对象或数组
                } else {
                  parsedItem[key] = value; // 保留原始值
                }
              } catch (e) {
                parsedItem[key] = value; // 如果解析失败，保留原始值
              }
            });
            console.log(storeName + "查询成功" + sql);
            return parsedItem;
          });

          resolve(parsedResult);
        },
        fail: (error: any) => {
          console.log(error);
          reject("加载数据失败: " + error.message);
        },
      });
    });
  }



  // 查询条数
  async countData(
    storeName: string,
    conditions: Record<string, any> = {}
  ): Promise<number> {
    if (!this.isOpenDB(this.dbName)) {
      throw new Error("数据库未初始化或未打开");
    }

    return new Promise((resolve, reject) => {
      let sql = `SELECT COUNT(*) as count FROM ${storeName}`;
      let whereClauses: string[] = [];

      // 获取 myUserId
      let myUserInfo = uni.getStorageSync("userInfo");
      let myUserId = myUserInfo ? myUserInfo.user_id : null;

      // 添加传入的条件
      Object.entries(conditions).forEach(([key, value]) => {
        whereClauses.push(`${key} = ${JSON.stringify(value)}`);
      });

      // 添加 myUserId 作为过滤条件
      if (myUserId) {
        whereClauses.push(`myUserId = ${JSON.stringify(myUserId)}`);
      }

      if (whereClauses.length) {
        sql += " WHERE " + whereClauses.join(" AND ");
      }

      plus.sqlite.selectSql({
        name: this.dbName,
        sql,
        success: (result: any) => {
          resolve(result[0].count);
        },
        fail: (error: any) => {
          reject(`查询 ${storeName} 的数据总条数失败: ${error.message}`);
        },
      });
    });
  }

  // 备份
  async backupDatabase(): Promise<string> {
    const data: Record<string, any[]> = {};

    // 遍历每个表
    for (const table of tables) {
      const items = await this.loadData(table.name, {
        page: 1,
        limit: Number.MAX_SAFE_INTEGER,
      });
      data[table.name] = items;
    }

    const jsonData = JSON.stringify(data, null, 2);
    console.log(jsonData);
    await this.saveToFile("backup.json", jsonData);
    return "备份成功";
  }

  // 保存到文件
  private async saveToFile(filename: string, data: string): Promise<void> {
    return new Promise((resolve, reject) => {
      plus.io.requestFileSystem(
        plus.io.PUBLIC_DOCUMENTS,
        (fs: any) => {
          fs.root?.getFile(
            filename,
            { create: true, exclusive: false },
            (fileEntry: any) => {
              fileEntry.createWriter((fileWriter: any) => {
                console.log("FileWriter created:", fileWriter);

                fileWriter.onwriteend = () => {
                  console.log("文件写入成功:", filename);
                  resolve();
                };

                fileWriter.onerror = (e: any) => {
                  console.error("文件写入失败:", e);
                  reject(`文件写入失败: ${e.message}`);
                };

                const blob: any = new Blob([data], {
                  type: "application/json",
                });

                console.log("准备写入数据:", blob);
                fileWriter.write(blob);
              });
            },
            (error: any) => {
              console.error("获取文件失败:", error);
              reject(`获取文件失败: ${error.message}`);
            }
          );
        },
        (error: any) => {
          console.error("请求文件系统失败:", error);
          reject(`请求文件系统失败: ${error.message}`);
        }
      );
    });
  }

  // 还原数据
  async restoreFromFile(filename: string): Promise<string> {
    return new Promise((resolve, reject) => {
      plus.io.requestFileSystem(
        plus.io.PUBLIC_DOCUMENTS,
        (fs: any) => {
          fs.root?.getFile(
            filename,
            {},
            (fileEntry: any) => {
              fileEntry.file((file: any) => {
                const reader = new FileReader();
                reader.onloadend = async (e) => {
                  try {
                    const data = JSON.parse(reader.result as string);
                    for (const tableName in data) {
                      await this.saveData(tableName, data[tableName]);
                    }
                    resolve("数据恢复成功");
                  } catch (err: any) {
                    reject(`恢复数据失败: ${err.message}`);
                  }
                };
                reader.readAsText(file);
              });
            },
            (error: any) => {
              reject(`获取文件失败: ${error.message}`);
            }
          );
        },
        (error: any) => {
          reject(`请求文件系统失败: ${error.message}`);
        }
      );
    });
  }

  // 执行 SQL 语句
  private executeSql(sql: string | string[]): Promise<void> {
    // 确保 sql 是一个数组
    const sqlArray = Array.isArray(sql) ? sql : [sql];

    return new Promise((resolve, reject) => {
      plus.sqlite.executeSql({
        name: this.dbName,
        sql: sqlArray,
        success: () => {
          console.log(`SQL 执行成功: ${JSON.stringify(sqlArray)}`);
          resolve();
        },
        fail: (error: any) => {
          console.error(
            `SQL 执行失败: ${JSON.stringify(sqlArray)}, 错误: ${JSON.stringify(
              error
            )}`
          );
          reject(error);
        },
      });
    });
  }
}

export default SQLite;
